package es.udc.emarketapp.emarket.model.client.dao;

import es.udc.emarketapp.emarket.model.client.vo.ClientVO;
import es.udc.emarketapp.emarket.model.user.dao.AbstractSQLUserDAO;
import es.udc.emarketapp.emarket.model.user.vo.UserVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;


public abstract class AbstractSQLClientDAO extends AbstractSQLUserDAO
        implements SQLClientDAO{

    public boolean exists(Connection connection, String idComprador)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_usuario FROM usuario "
                    + "WHERE id_usuario = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idComprador);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
    }//exists

    public boolean exists(Connection connection, String idComprador, String pass) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean existsNifCif(Connection connection, String nifCif)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT nif_cif FROM usuario "
                    + "WHERE nif_cif = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, nifCif);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
    }

    public ClientVO findById(Connection connection, String idComprador)
            throws InternalErrorException, InstanceNotFoundException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            
            // Create "preparedStatement".
            String queryString = "SELECT nom_pila, ap1, ap2, nif_cif, dir, "
                    + "ciudad, cp, prov, pais, email, tlf1, tlf2, tipo_usuario, "
                    + "aprobado, valido FROM usuario WHERE id_usuario = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idComprador);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idComprador,
                        UserVO.class.getName());
            }

            // Get results.
            int i = 1;
            String nomPila = resultSet.getString(i++);
            String ap1 = resultSet.getString(i++);
            String ap2 = resultSet.getString(i++);
            String nifCif = resultSet.getString(i++);
            String dir = resultSet.getString(i++);
            String ciudad = resultSet.getString(i++);
            String cp = resultSet.getString(i++);
            String prov = resultSet.getString(i++);
            String pais = resultSet.getString(i++);
            String email = resultSet.getString(i++);
            String tlf1 = resultSet.getString(i++);
            String tlf2 = resultSet.getString(i++);
            String tipoUsuario = resultSet.getString(i++);
            boolean aprobado = resultSet.getBoolean(i++);
            boolean valido = resultSet.getBoolean(i++);


             // Create "preparedStatement".
            queryString = "SELECT f_nac, dir_envio, ciudad_envio, cp_envio, "
                    + "prov_envio, pais_envio FROM comprador "
                    + "WHERE id_comprador = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idComprador);

             // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idComprador,
                        ClientVO.class.getName());
            }

            // Get results.
            i = 1;
            Calendar fNac = Calendar.getInstance();
            fNac.setTimeInMillis((resultSet.getTimestamp(i++)).getTime());
            String dirEnvio = resultSet.getString(i++);
            String ciudadEnvio = resultSet.getString(i++);
            String cpEnvio = resultSet.getString(i++);
            String provEnvio = resultSet.getString(i++);
            String paisEnvio = resultSet.getString(i++);


            // Return the value object.
            return new ClientVO(idComprador, null, nomPila, ap1,
                ap2, nifCif, dir, ciudad, cp, prov, pais, email, tlf1, tlf2,
                tipoUsuario,  aprobado, valido, fNac, dirEnvio, ciudadEnvio,
                cpEnvio, provEnvio, paisEnvio);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
    }


    public ClientVO updateClient(Connection connection, ClientVO clientVO)
            throws InternalErrorException, InstanceNotFoundException {
 

        PreparedStatement preparedStatement = null;
        
        try {

            // Create "preparedStatement".
            String query = "UPDATE usuario "
                    + "SET ultima_act = ?, nom_pila = ?, ap1 = ?, "
                    + "ap2 = ?, nif_cif = ?, dir = ?, "
                    + "ciudad = ?, cp = ?, prov = ?, pais = ?, "
                    + "email = ?, tlf1 = ?, tlf2 = ? "
                    + "WHERE id_usuario = ? ";

            preparedStatement = connection.prepareStatement(query);

         //Ahora lo llenamos con los parametros que se quiere
            int i = 1;
            preparedStatement.setTimestamp(i++,
                    new Timestamp(Calendar.getInstance().getTime().getTime()));
            preparedStatement.setString(i++, clientVO.getNomPila());
            preparedStatement.setString(i++, clientVO.getAp1());
            preparedStatement.setString(i++, clientVO.getAp2());
            preparedStatement.setString(i++, clientVO.getNifCif());
            preparedStatement.setString(i++, clientVO.getDir());
            preparedStatement.setString(i++, clientVO.getCiudad());
            preparedStatement.setString(i++, clientVO.getCp());
            preparedStatement.setString(i++, clientVO.getProv());
            preparedStatement.setString(i++, clientVO.getPais());
            preparedStatement.setString(i++, clientVO.getEmail());
            preparedStatement.setString(i++, clientVO.getTlf1());
            preparedStatement.setString(i++, clientVO.getTlf2());
            preparedStatement.setString(i++, clientVO.getIdComprador());
      

            // Execute query.
            int insertedRows = preparedStatement.executeUpdate();

            if (insertedRows == 0) {
                throw new SQLException("No se pudo insertar la fila a la tabla"
                        + " 'Usuario'");
            }

            if (insertedRows > 1) {
                throw new SQLException("Fila duplicada en la tabla 'Usuario'");
            }


            //*************************

            //Creamos el statement

            query = "UPDATE comprador "
                    + "SET f_nac = ?, dir_envio = ?, ciudad_envio = ?, "
                    + "cp_envio = ?, prov_envio = ?, pais_envio = ? "
                    + "WHERE id_comprador = ? ";


            preparedStatement = connection.prepareStatement(query);

            //Ahora lo llenamos con los parametros que se quiere
            i = 1;
            preparedStatement.setTimestamp(i++,
                    new Timestamp(clientVO.getFNac().getTimeInMillis()));
            preparedStatement.setString(i++, clientVO.getDirEnvio());
            preparedStatement.setString(i++, clientVO.getCiudadEnvio());
            preparedStatement.setString(i++, clientVO.getCpEnvio());
            preparedStatement.setString(i++, clientVO.getProvEnvio());
            preparedStatement.setString(i++, clientVO.getPaisEnvio());
            preparedStatement.setString(i++, clientVO.getIdComprador());

            // Execute query.
            insertedRows = preparedStatement.executeUpdate();

            if (insertedRows == 0) {
                throw new SQLException("No se pudo insertar la fila a la tabla"
                        + " 'Comprador'");
            }

            if (insertedRows > 1) {
                throw new SQLException("Fila duplicada en la tabla 'Comprador'");
            }

            return clientVO;

        } catch (SQLException e) {
        	//System.out.println(e);
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }


    public ClientVO findById(Connection connection, String idComprador, String pass) throws InternalErrorException, InstanceNotFoundException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<ClientVO> findByNameSurnameNif(Connection connection, String nomePila, String ap1, String ap2, String nifCif, int startIndex, int count) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<ClientVO> findAll(Connection connection, int startIndex, int count) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<ClientVO> findByPais(Connection connection, String pais, int startIndex, int count) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }


}
